﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.EntitesDAL
{
    public class PhieuthuDAL : BaseDAL, UTL.IBaseDAL
    {
        #region Implement

        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            return ExecuteQuery("sp_SelectAll_Phieuthu");
        }

        public DataTable Select_View(string _makhoa = null)
        {
            try
            {
                var sql = @"set dateformat dmy; execute sp_SelectAll_Phieuthu_View '{0}' ";
                sql = string.Format(sql, _makhoa);
                return ExecuteQuery(sql);
            }
            catch { return null; }
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            var sql = "sp_Delete_Phieuthu {0}";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (Phieuthu)obj;
            var sql = "set dateformat dmy; execute sp_Insert_Phieuthu '{0}', '{1}', '{2}', '{3}', N'{4}', N'{5}' ";
            sql = string.Format(sql, o.SoHD, o.MaHVID, o.Ngaylap, o.Sotien, o.Bangchu, o.Ghichu);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (Phieuthu)obj;
            var sql = "set dateformat dmy; execute sp_Update_Phieuthu '{0}', '{1}', '{2}', '{3}', N'{4}', N'{5}'  ";
            sql = string.Format(sql, o, o.SoHD, o.MaHVID, o.Ngaylap, o.Sotien, o.Bangchu, o.Ghichu);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            var sql = @"select * from tb_Phieuthu where Ghichu like N'%{0}%' ";
            sql = string.Format(sql, name);
            return ExecuteQuery(sql);
        }


        #endregion

        #region Other

        public DataTable Select_Max()
        {
            var sql = @"Select Max(SoHD) as Ma From tb_Phieuthu";
            return ExecuteQuery(sql);
        }

        #endregion

        #region Doanh thu

        /// <summary>
        /// Doanh thu lop hoc trong khoa
        /// </summary>
        /// <param name="id"> makhoa </param>
        /// <returns> data table </returns>
        public DataTable Select(String id = null)
        {
            try
            {
                string sql = @"  Select a.MalopID, SUM(b.Sotien) as Tien into #temp
                            From tb_Hocvien a join tb_Phieuthu b on a.MaHV=b.MaHVID
					                          join tb_Lophoc c on a.MalopID=c.Malop
                            Where c.MakhoaID = '{0}'
                            Group by a.MalopID

                            Select c.MalopID,a.Tenlop,c.Tien, b.Tenkhoa
                            From tb_Lophoc a join #temp c on a.Malop=c.MalopID
											 join tb_Khoahoc b on a.MakhoaID=b.Makhoa  ";
                sql = string.Format(sql, id);
                return ExecuteQuery(sql);
            }
            catch { return null; }
        }

        public DataTable Select_Sum(string id = null)
        {
            try
            {
                string sql = @" Select a.MalopID, SUM(b.Sotien) as Tien into #temp
                            From tb_Hocvien a join tb_Phieuthu b on a.MaHV=b.MaHVID
					                          join tb_Lophoc c on a.MalopID=c.Malop
                            Where c.MakhoaID = '{0}'
                            Group by a.MalopID

                            Select SUM(Tien) as Tongtien into #tmp
                            From #temp a join tb_Lophoc b on a.MalopID=b.Malop

                            Select * From #tmp	  ";
                sql = string.Format(sql, id);
                return ExecuteQuery(sql);
            }
            catch { return null; }
        }

        #endregion

        #region Hóa đơn

        public DataTable Select_InHD(string _soHD)
        {
            string sql = @" Select SoHD,b.Hoten,b.Diachi,Ngaylap,Sotien,Bangchu,Ghichu,c.Tenlop,d.Tenkhoa
                            From tb_Phieuthu a join tb_Hocvien b on a.MaHVID = b.MaHV
					                           join tb_Lophoc c on b.MalopID=c.Malop
					                           join tb_Khoahoc d on c.MakhoaID=d.Makhoa
                            Where SoHD = '{0}' ";
            sql = string.Format(sql,_soHD);
            return ExecuteQuery(sql);
        }

        #endregion
    }
}
